********************************************************************************
* descriptive_stats.do
* Purpose: Produce descriptive statistics figures and tables.
*
* Figure 1   : Total M&A deal count by year (eligible vs. matched sample)
*              Dual-axis plot: total eligible deals (left axis) and matched-sample
*              deals (right axis), for 2005-2017, excluding NAICS 52 and 55.
*              Input : $data/NALMF_mna.dta, $data/firm_matched_list.dta
*              Output: $out/Figure1.pdf, $out/number_mna_deal.csv
*
* Table 1    : Summary statistics for the matched firm and worker samples,
*              measured one year before the M&A event (t = -1).
*              Columns: acquirer firm, control firm, diff (p-value),
*                       target firm, control firm, diff (p-value).
*              Panel A: Firm characteristics (revenue, expenses, employment,
*                       payroll, leverage, markups)
*              Panel B: Sector distribution of matched firms
*              Panel C: Worker characteristics (earnings, age, gender)
*              Panel D: Sector distribution of matched workers
*              Input : $data/firm_matched.dta, $data/worker_matched.dta
*              Output: $out/table1.csv
*
* Figure A9  : Exogenous Mobility Assumption (AKM validation test, Appendix A.9)
*              Mean log earnings of job movers by quartile of AKM firm fixed
*              effects at origin (t = -1) and destination (t = 0) firms.
*              Workers must be movers who stayed at origin in t-2 and t-1 and
*              stayed at destination in t=0 and t=1.
*              Input : $data/worker_firm_panel.dta, $data/canada_cpi.dta,
*                      $data/first_mna.dta, $data/firm_level_emp.dta, $data/akm.dta
*              Output: $out/FigureA9.pdf, $out/figureA9_data.xlsx,
*                      $data/post_akm_data.dta
*
* Table A9   : Descriptive statistics on unmatched eligible M&A firms and workers
*              (Appendix A.8), measured one year before the event (t = -1),
*              restricted to eligible firms/workers not selected into the matched
*              sample. Columns: acquirer, target (no control group).
*              Panel A: Firm characteristics (revenue, expenses, employment,
*                       payroll, leverage, markups)
*              Panel B: Sector distribution of eligible unmatched firms
*              Panel C: Worker characteristics (earnings, age, gender)
*              Panel D: Sector distribution of eligible unmatched workers
*              Input : $data/firm_eligible.dta, $data/worker_eligible.dta
*              Output: $out/tableA9.csv
*
* Figure A11 : Probability of Exit and Transition to Unemployment
*              Panel A only: firm exit rate by event-time for target firms vs.
*              matched control firms (exit = last year observed before 2017).
*              Note: Panel B (worker transition to unemployment) is not produced
*              by this script.
*              Input : $data/firm_matched.dta
*              Output: $out/figure_FigureA11_A.pdf, $out/FigureA11_data.csv
*
* Figure A12 : Propensity Score Matching Overlap
*              Panel A: Distribution of propensity scores for matched firms
*                       (treated vs. control), bin width = 0.01.
*              Panel B: Distribution of propensity scores for matched workers
*                       (treated vs. control), bin width = 0.02.
*              Input : $data/firm_matched_list.dta, $data/worker_matched_list.dta
*              Output: $out/figure_figureA12_A.pdf, $out/figure_figureA12_B.pdf,
*                      $out/firm_pscore_overlap.dta, $out/worker_pscore_overlap.dta
********************************************************************************
*	   descriptive_stats.do   *
********************************************************************************
set more off

*********************************************
**# Figure 1
*********************************************
{
use $data/NALMF_mna, replace

keep if inrange(DEAL_YEAR, 2005, 2017)
drop if naics2 == 52 | naics2 == 55

*** Total number of deals
preserve
{
	
keep if DEAL_YEAR == year
duplicates drop DEAL, force
eststo tot_deal: estpost tabstat DEAL, by(DEAL_YEAR) stat(count)
}

restore
preserve
{
gen treated = 1
gen year_prior = DEAL_YEAR - 1

joinby entid_syn using $data/firm_matched_list, unmatched(master)
gen		matched	= _merge == 3

keep if year == year_prior

gen 	matched_with_restiction	=	2	
replace matched_with_restiction	=	.	if PD7_AvgEmp_NonZero < 10
replace matched_with_restiction	=	.	if mi(naics) | mi(total_revenue) | mi(age) | mi(avg_wage)

gsort matched_with_restiction -matched

duplicates drop DEAL, force

eststo tot_multi_deal: estpost tabstat DEAL if matched_with_restiction == 2, by(DEAL_YEAR) stat(count)

eststo tot_multi_matched_deal: estpost tabstat DEAL if matched == 1, by(DEAL_YEAR) stat(count)
}

#delimit ;
esttab tot_deal tot_multi_deal tot_multi_matched_deal  using $out/number_mna_deal, 
	cells( count(fmt(0)) ) noobs replace csv 
	label  fragment type collabel(" ") plain
	mtitle("Total" "Multi Qualified" "Multi Matched");
#delimit cr
	
esttab tot_*, cells(count)

import delimited using $out/number_mna_deal, clear

drop in 1/2
drop if v1 == "Total"

rename (v1 v2 v3 v4) (DEAL_YEAR Total MultiQualified MultiMatched)
destring, replace

local color1 "black"
local color2 "red"
label var MultiQualified	"Number of Deals"
label var MultiMatched		"Number of Deals in Matched Sample "
label var DEAL_YEAR			"Year"

#delimit ;
tw 	connected MultiQualified DEAL_YEAR, ytitle("Number of Deals") 
	lcolor(`color1' `color2') msize(medlarge medlarge) mc(`color1' `color2')  lw(0.4 0.4) mfc(none none)
	lpattern(solid solid)  || 
	connected MultiMatched DEAL_YEAR, yaxis(2) ylabel(300 400 500 600, axis(2)) ytitle("Number of Deals in Matched Sample ", axis(2))
	xlabel(2005(2)2017)
	lcolor(`color2') msize(medlarge medlarge) mc(`color2')  lw(0.4 0.4) 
	lpattern(solid solid) 
	legend(pos(6) col(1) ring(1))
	graphregion(color(white)) bgcolor(white);	
	
#delimit cr

gr export $out/Figure1.pdf, replace
restore, not
}

*********************************************************
* 			Pooled Data - Descriptive Stats				*
*********************************************************
use $data/firm_matched, clear

*** Define Summary Statistics Variables ***
gen netprof			= (total_revenue - total_expense)/total_revenue
gen	lev				= 1 - total_shareholder_equity/total_assets
gen markup 			= theta_ct*(sales_goods_and_services/total_cost_of_sales)
gen emp				= PD7_AvgEmp_NonZero
gen roa 			= (total_revenue - total_expense)/total_assets

*********************************************
**# Table 1 - Panel A
*********************************************
preserve
{
replace	total_revenue 	= total_revenue/1e6
replace total_expense	= total_expense/1e6
gegen 	num_firm 		= nunique(id), by(treated matched_acq)

local var_list "total_revenue total_expense netprof emp avg_payroll lev markup num_firm"
gstats winsor `var_list', replace cuts(5 95) by (year) 

keep if t == -1

** Creating the table
label var total_revenue "Total Revenue (in millions)"
label var total_expense	"Total Expenses (in millions)"
label var netprof		"Profit Margins"
label var emp 			"Number of Employees"
label var avg_payroll	"Average Payrolls"
label var lev 			"Leverage Ratio"
label var markup		"Markups"
label var num_firm 		"Number of Firms"

gen flip_treated = 1 - treated
foreach v in `var_list' {
	
		eststo adiff_`v': estpost ttest	  `v' if matched_acq	==	1, by(flip_treated) unequal
		eststo tdiff_`v': estpost ttest	  `v' if matched_acq	==	0, by(flip_treated) unequal
}

#delimit ;

file open mycsv using $out\table1.csv, write replace;
file write mycsv " & (1) & (2) & (3) & (4) & (5) & (6) \\" _n;
file write mycsv " & Acquirer Firm & Control Firm & Diff(P-Value) & Target Firm & Control Firm & Diff(P-Value) \\" _n;
file write mycsv " Panel A: Firm Characteristics \\" _n;
file close mycsv;

foreach v in `var_list' {;
	local replace_append 	"append";
	local title 			"plain label fragment type collabel(" ") nomtitle";

	if `v' == total_revenue | `v' == total_expense | `v' == emp | `v' == avg_payroll {;
		local cell_format "cells("mu_1(fmt(0)) mu_2(fmt(0)) b(fmt(0)) & p(fmt(2) par)") noobs";
	};
	else if `v' == lev | `v' == netprof | `v' == markup {;
		local cell_format "cells("mu_1(fmt(2)) mu_2(fmt(2)) b(fmt(2)) & p(fmt(2) par)") noobs";
	};
	else{;
		local cell_format "cells("N_1(fmt(0)) N_1(fmt(0)) .") noobs";
	};
	
	esttab adiff_`v' tdiff_`v' using $out\table1, `cell_format' `replace_append' `title' csv;
};

#delimit cr
}

*********************************************
**# Table 1 - Panel B
*********************************************
restore, preserve
{
estimates clear
#delimit ;

label define sec_label  23 "Construction (23)"
						31 "Manufacturing (31)"
						41 "Wholesale (41)"
						44 "Retail (44)"
						48 "Transporation (48)"
						51 "Information (51)"
						54 "Services (54)"
						99 "Other", replace;
label value naics2 sec_label;

#delimit cr

replace naics2 = 99 if naics2 == 11 | naics2 == 21 | naics2 == 22 | naics2 == 53
replace naics2 = 99 if naics2 >= 62

keep if t == -1 
collapse (count) firm_count = id, by(naics2 Acquirer)

gegen 	total_count = total(firm_count), by(Acquirer)
gen		share		= firm_count/total_count

foreach i of numlist 1 0{
	
	eststo share_`i': 	estpost tabstat share 		if Acquirer == `i', by(naics2) nototal stats(mean)
}

#delimit ;

file open mycsv using $out\table1.csv, write append;
file write mycsv " \\" _n;
file write mycsv " Panel B: Sectors (Firms) \\" _n;
file write mycsv " \\" _n;
file close mycsv;

esttab share_1 share_1 share_1 share_0 share_0 using $out\table1, 
	cells( mean(fmt(2))  ) noobs append csv 
	label  fragment type collabel(" ") plain nomtitle;
	
#delimit cr

}
restore, not
*********************************************
**# Table 1 - Panel C
*********************************************
use $data/worker_matched, clear
{
gen 	age 	= t1_age
gen 	gender 	= t1_sex - 1
gegen 	count 	= nunique(id), by(treated matched_acq)

label var total_wage 	"Total Earnings"
label var age			"Age"
label var gender		"Female"
label var count 		"Number of Workers"

gstats winsor total_wage, replace cuts(5 95) by(year) 

local var_list "total_wage age gender count"
keep if t  == -1

gen flip_treated = 1 - treated

foreach v in `var_list' {
	
	eststo adiff_`v': estpost ttest	  `v' if matched_acq	==	1, by(flip_treated) unequal
	eststo tdiff_`v': estpost ttest	  `v' if matched_acq	==	0, by(flip_treated) unequal
}

#delimit ;

file open mycsv using $out\table1.csv, write append;
file write mycsv "\\" _n;
file write mycsv "Panel C: Worker Characteristics \\" _n;
file close mycsv;

foreach v in `var_list' {;
	local replace_append 	"append";
	local title 			"plain label fragment type collabel(" ") nomtitle";

	if `v' == total_wage {;
		local cell_format "cells("mu_1(fmt(0)) mu_2(fmt(0)) b(fmt(0)) & p(fmt(2) par)") noobs";
	};
	else if `v' == age {;
		local cell_format "cells("mu_1(fmt(2)) mu_2(fmt(2)) b(fmt(2)) & p(fmt(2) par)") noobs";
	};
	else if `v' == gender {;
		local cell_format "cells("mu_1(fmt(1)) mu_2(fmt(1)) b(fmt(1)) & p(fmt(2) par)") noobs";
	};
	else{;
		local cell_format "cells("N_1(fmt(0)) N_1(fmt(0)) .") noobs";
	};
	
	esttab adiff_`v' tdiff_`v' using $out\table1, `cell_format' `replace_append' `title' csv;
};

#delimit cr
}
*********************************************
**# Table 1 - Panel D
*********************************************
use $data/worker_matched, clear
{
** Distribution by Sector **
estimates clear

#delimit ;

label define sec_label  23 "Construction (23)"
						31 "Manufacturing (31)"
						41 "Wholesale (41)"
						44 "Retail (44)"
						48 "Transporation (48)"
						51 "Information (51)"
						54 "Services (54)"
						99 "Other", replace;
label value naics2 sec_label;

#delimit cr

drop if naics2 == 52 | naics2 == 55
replace naics2 = 99 if naics2 == 11 | naics2 == 21 | naics2 == 22 | naics2 == 53
replace naics2 = 99 if naics2 >= 62

keep	if t == -1
collapse (count) id, by(naics2 Acquirer)

gegen 	total_count = total(id), by(Acquirer)
gen		share		= id/total_count

foreach i in 1 0{
	eststo share_`i': estpost tabstat share if Acquirer == `i', by(naics2) stats(mean) nototal
}


#delimit ;

file open mycsv using $out\table1.csv, write append;
file write mycsv " \\" _n;
file write mycsv " Panel D: Sectors (Workers) \\" _n;
file write mycsv " \\" _n;
file close mycsv;

esttab share_1 share_1 share_0 share_0 using $out\table1, 
	cells( mean(fmt(2)) ) noobs append csv nomtitle
	label  fragment type collabel(" ") plain;

#delimit cr
}

***********************
* Figure A9 *
* AKM Assumption Test *
***********************
{
use $data/worker_firm_panel, clear

* identify never-movers
gsort casenum2019 year entid_syn
by casenum2019: gen gap		= year[_n+1] - year
gen unemployed = ( gap > 1 & ~mi(gap) )

gsort casenum2019 entid_syn year
gegen last_year_of_data	 = max(year), by(casenum2019)
by casenum2019 entid_syn: gen year_forw = year[_n+1] - year

gen		moved	= 1 if year == last_year_at_firm
replace moved	= 1 if year_forw > 1 & ~mi(year_forw)
replace moved	= 1 if unemployed == 1
replace moved	= 0 if year == last_year_of_data
replace moved	= 0 if mi(moved)
gegen ever_moved	=	max(moved), 	by(casenum2019)


* merge in CPI data, first MnA data
merge m:1 year 	using $data/canada_cpi, keep(1 3) nogen
gen 	CPI_base_2011 	= CPI/119.9
gen		t4earn_adjusted = t4earn/CPI_base_2011
gen 	logearnings		= log(t4earn_adjusted)

merge m:1 entid_syn 		using $data/first_mna, 		keep(1 3) keepusing(DEAL_YEAR) nogen
merge m:1 entid_syn year 	using $data/firm_level_emp, keep(1 3) keepusing(PD7_AvgEmp_NonZero) nogen


gegen worker_id		=	group(casenum2019)
gegen firm_id		=	group(entid_syn)

*** Merge in CPI data, first MnA data ***
merge m:1 entid_syn 		using $data/akm, 				keep(1 3) keepusing(fe) nogen

* keep the dominant firm i.e. the highest-paying firm
gsort worker_id year -t4earn

bys worker_id year: gen worker_seq = _n


drop if ever_moved	==	0
drop if t4earn_adjusted < 3900 
drop if mi(t4earn_adjusted)
drop if year == DEAL_YEAR

keep if worker_seq == 1
xtset worker_id year

bysort worker_id: gen firm0 = firm_id
bysort worker_id: gen firm_1 = L.firm_id
bysort worker_id: gen firm_2 = L2.firm_id
bysort worker_id: gen firm_3 = L3.firm_id
bysort worker_id: gen firm1 = F.firm_id

// Same employer in year-1 and year-2
gen stayer_before = (firm_1 == firm_2 & firm_1!=. & firm_2!=.)

// Same employer in year-1, year-2, and year-3
gen stayer_before_3 = (firm_1 == firm_2 & firm_2 == firm_3 & firm_1!=. & firm_2!=. & firm_3!=.)

// Change of employer from year-1 to year 0
gen mover = (firm0 != firm_1 & firm0 !=. & firm_1!=.)

// Same employer in year 0 and year 1
gen stayer_after = (firm0 == firm1 & firm0 !=. & firm1!=.)

// Earnings
bysort worker_id: gen logearnings_fe0 = fe
bysort worker_id: gen logearnings_fe_1 = L.fe
bysort worker_id: gen logearnings_fe_2 = L2.fe
bysort worker_id: gen logearnings_fe_3 = L3.fe
bysort worker_id: gen logearnings_fe1 = F.fe

bysort worker_id: gen logearnings0 = logearnings
bysort worker_id: gen logearnings_1 = L.logearnings
bysort worker_id: gen logearnings_2 = L2.logearnings
bysort worker_id: gen logearnings_3 = L3.logearnings
bysort worker_id: gen logearnings1 = F.logearnings

gquantiles logearningsfe_xtile0 = logearnings_fe0 , xtile n(4)
gquantiles logearningsfe_xtile_1 = logearnings_fe_1 , xtile n(4)

gen logearningsfe_xtile0_N = logearningsfe_xtile0
gen logearningsfe_xtile_1_N = logearningsfe_xtile_1

compress
save $data/post_akm_data.dta, replace

*******************************
**# Figure A9 - data
*******************************
use $data/post_akm_data, clear
gcollapse (mean) logearnings_3 logearnings_2 logearnings_1 logearnings0 logearnings1 (count) logearningsfe_xtile_1_N logearningsfe_xtile0_N ///	
		if mover==1 & stayer_before==1 & stayer_after==1 , by(logearningsfe_xtile_1 logearningsfe_xtile0)
		gen delta = logearnings1-logearnings_3
		gen adj = . in 1/16
		replace adj = delta[1] if logearningsfe_xtile_1 == 1
		replace adj = delta[6] if logearningsfe_xtile_1 == 2
		replace adj = delta[11] if logearningsfe_xtile_1 == 3
		replace adj = delta[16] if logearningsfe_xtile_1 == 4
		gen delta_adj = delta-adj
		drop adj
		rename logearningsfe_xtile_1_N N_obs
		drop logearningsfe_xtile0_N
		order logearningsfe_xtile_1 logearningsfe_xtile0 logearnings_3 logearnings_2 logearnings_1 logearnings0 logearnings1 delta   delta_adj N_obs 
export excel using $out/figureA9_data.xlsx, firstrow(variables) sheet("PanelA", replace) 

import excel $out/figureA9_data.xlsx, firstrow sheet("PanelA") clear

rename logearningsfe_xtile_1 origin
rename logearningsfe_xtile0 destination

drop if mi(origin) | mi(destination)

rename (logearnings_3 logearnings_2 logearnings_1 logearnings0 logearnings1) (wage1 wage2 wage3 wage4 wage5)
gegen i = group(origin destination)

reshape long wage, i(i) j(time)
replace time = time - 4

#delimit ;

twoway connected wage time if origin == 4 & destination == 4, msymbol(O) lpattern(solid)
|| connected wage time if origin == 4 & destination == 3, msymbol(D) lpattern(solid)
|| connected wage time if origin == 4 & destination == 2, msymbol(T) lpattern(solid)
|| connected wage time if origin == 4 & destination == 1, msymbol(S) lpattern(solid)
|| connected wage time if origin == 1 & destination == 4, msymbol(O) lpattern(dash)
|| connected wage time if origin == 1 & destination == 3, msymbol(D) lpattern(dash)
|| connected wage time if origin == 1 & destination == 2, msymbol(T) lpattern(dash)
|| connected wage time if origin == 1 & destination == 1, msymbol(S) lpattern(dash)
legend(order(1 "4 to 4" 2 "4 to 3" 3 "4 to 2" 4 "4 to 1" 5 "1 to 4" 6 "1 to 3" 7 "1 to 2" 8 "1 to 1") col(4))
xtitle("Time (-1=Last Year at Origin; 0=First Year at Destination)")
ytitle("Mean Log Earnings of Movers") 
ylabel(, format(%9.1fc) grid gmax gmin)
graphregion(color(white)) bgcolor(white);	

#delimit cr

gr export $out/FigureA9.pdf, replace
}
*****************************************
**#	Table A9 - Panel A
******************************************
{
use $data/firm_eligible, clear

*** Define Summary Statistics Variables ***
gen	lev				= 1 - total_shareholder_equity/total_assets
gen markup 			= theta_ct*(sales_goods_and_services/total_cost_of_sales)
gen emp				= PD7_AvgEmp_NonZero
gen roa 			= (total_revenue - total_expense)/total_assets

*********************************************
**# Table A9 - Panel A
*********************************************
replace	total_revenue 	= total_revenue/1e6
replace total_expense	= total_expense/1e6

local var_list "total_revenue total_expense emp avg_payroll lev markup"
gstats winsor `var_list', replace cuts(5 95) by (matched year) 

keep if t == -1
keep if matched == 0

** Creating the table
label var total_revenue "Total Revenue (in millions)"
label var total_expense	"Total Expenses (in millions)"
label var emp 			"Number of Employees"
label var avg_payroll	"Average Wage Bill"
label var lev 			"Leverage Ratio"
label var markup		"Markups"

foreach v in `var_list' {
		eststo amean_`v': estpost tabstat `v' if matched_acq == 1, stats(mean)
		eststo tmean_`v': estpost tabstat `v' if matched_acq == 0, stats(mean)
}
#delimit ;

foreach v in `var_list' {;

	if `v' == total_revenue {;
	
		esttab amean_`v' tmean_`v' using $out\tableA9, 
			cells( "mean(fmt(0))") noobs replace csv 
			label  fragment type collabel(" ") 
			mtitle("Acquirer" "Target" );
	};
	else if `v' == lev | `v' == markup {;
	
		esttab amean_`v' tmean_`v'  using $out\tableA9, 
			cells( "mean(fmt(2))")  plain noobs append csv 
			label fragment type collabel(" ") 
			nomtitle;
	};
	else {;
	
		esttab amean_`v' tmean_`v'  using $out\tableA9, 
			cells( "mean(fmt(0))")  plain noobs append csv 
			label fragment type collabel(" ") 
			nomtitle;
	
	};
};
esttab amean_total_revenue tmean_total_revenue  using $out\tableA9, 
	plain append csv 
	label fragment type collabel(" ") 
	nomtitle;

	
#delimit cr
}
*********************************************
**# Table A9 - Panel B
*********************************************
{
use $data/firm_eligible, clear
estimates clear

#delimit ;

label define sec_label  23 "Construction (23)"
						31 "Manufacturing (31)"
						41 "Wholesale (41)"
						44 "Retail (44)"
						48 "Transporation (48)"
						51 "Information (51)"
						54 "Services (54)"
						99 "Other", replace;
label value naics2 sec_label;

#delimit cr

drop if matched_sector == 52 | matched_sector == 55
replace naics2 = 99 if naics2 == 11 | naics2 == 21 | naics2 == 22 | naics2 == 53
replace naics2 = 99 if naics2 >= 62

keep if t == -1 
keep if matched == 0
collapse (count) firm_count = id, by(naics2 Acquirer)

gegen 	total_count = total(firm_count), by(Acquirer)
gen		share		= firm_count/total_count

foreach i of numlist 1 0{
	
	eststo share_`i': 	estpost tabstat share 		if Acquirer == `i', by(naics2) nototal stats(mean)
}

#delimit ;

esttab share_1 share_0 using $out\tableA9, 
	cells( mean(fmt(2)) ) noobs append csv 
	label  fragment type collabel(" ") plain
	mtitle("Acquirer" "Target" );
	
#delimit cr
}
*********************************************
**# Table A9 - Panel C
*********************************************
{
use $data/worker_eligible, clear

gen 	age 	= t1_age
gen 	gender 	= t1_sex - 1

gstats winsor total_wage, replace cuts(5 95) by(matched year) 

local var_list "total_wage age gender count"
keep if t  == -1
keep if matched == 0

gegen 	count 	= nunique(id), by(treated matched_acq)

label var total_wage 	"Total Earnings"
label var age			"Age"
label var gender		"Female"
label var count 		"Number of Workers"

foreach v in `var_list' {
	eststo a`v'	: estpost tabstat `v' 		 if matched_acq == 1, stats(mean sd) 
	eststo t`v'	: estpost tabstat `v' 		 if matched_acq == 0, stats(mean sd) 
}

#delimit ;

esttab atotal_wage ttotal_wage using $out\tableA9, 
	cells( "mean(fmt(0))") plain noobs append csv 
	label  fragment type collabel(" ") nomtitle;
		
esttab aage tage using $out\tableA9, 
	cells( "mean(fmt(1))") plain noobs append csv nomtitle
	label fragment type collabel(" ")
	nomtitle;
	
esttab agender tgender using $out\tableA9, 
	cells( "mean(fmt(2))") plain noobs append csv nomtitle
	label fragment type collabel(" ")
	nomtitle;
	
esttab acount tcount using $out\tableA9, 
	cells( mean(fmt(0)) ) plain noobs append csv nomtitle
	label fragment type collabel(" ")
	nomtitle;
	
#delimit cr	
}
*********************************************
**# Table A9 - Panel D
*********************************************
{
use $data/worker_eligible, clear

** Distribution by Sector **
estimates clear

#delimit ;

label define sec_label  23 "Construction (23)"
						31 "Manufacturing (31)"
						41 "Wholesale (41)"
						44 "Retail (44)"
						48 "Transporation (48)"
						51 "Information (51)"
						54 "Services (54)"
						99 "Other", replace;
label value naics2 sec_label;

#delimit cr

drop if naics2 == 52 | naics2 == 55
replace naics2 = 99 if naics2 == 11 | naics2 == 21 | naics2 == 22 | naics2 == 53
replace naics2 = 99 if naics2 >= 62

keep if t == -1
keep if matched == 0
collapse (count) id, by(naics2 Acquirer)

gegen 	total_count = total(id), by(Acquirer)
gen		share		= id/total_count

foreach i in 1 0{
	eststo share_`i': estpost tabstat share if Acquirer == `i', by(naics2) stats(mean) nototal
}


#delimit ;

esttab share_1 share_0 using $out\tableA9, 
	cells( mean(fmt(2)) ) noobs append csv 
	label  fragment type collabel(" ") plain nomtitle;

#delimit cr
}

*********************************************
**# Figure A11
*********************************************
*********************************************
**# Figure A11
*********************************************
{
use $data/firm_matched, clear

gegen 	last_year_in_data	= 	max(year), by(entid_syn)
gen		exit 				= 	last_year_in_data == year	&	year ~= 2017

gen post = t >= 0
eststo exit_m_acq: estpost tabstat exit if treated == 1 & matched_acq == 1, by(t) stats(mean sum count)
eststo exit_c_acq: estpost tabstat exit if treated == 0 & matched_acq == 1, by(t) stats(mean sum count)
eststo exit_m_tar: estpost tabstat exit if treated == 1 & matched_acq == 0, by(t) stats(mean sum count)
eststo exit_c_tar: estpost tabstat exit if treated == 0 & matched_acq == 0, by(t) stats(mean sum count)

* Figure A11 footnote
reg exit post##treated if matched_acq == 0


#delimit ;

esttab exit_* using $out/FigureA11_data, 
			cells( mean(fmt(3) ) ) noobs replace csv 
			label  fragment type collabel(" ") 
			mtitle("Acquirer Firm" "Control Firm" "Target Firm" "Control Firm");

#delimit cr

*** firm exit rate ***
import delimited using $out/FigureA11_data.csv, clear

keep v1 v4 v5

rename (v1 v4 v5) (t target control)

drop in 1/3
drop if _n==_N
foreach v of varlist t target control{
	replace `v' = subinstr(`v', "=", " ", .)
	replace `v' = subinstr(`v', `"""', "", .)
}
destring, replace
replace t = t + 1

save tmp, replace

clear
set obs 10
gen t = _n -5 

merge 1:1 t using tmp, keep(1 3) nogen

replace target = 0 if missing(target)
replace control = 0 if missing(control)

local color1 "dkorange"
local color2 "black"
label var target 	"Target Firms"
label var control	"Control Firms"
local fmt "%9.2fc"

#delimit ;
tw 	connected target control t, ytitle("Exit Rate") 
	lcolor(`color1' `color2') msize(medlarge medlarge) mc(`color1' `color2')  lw(0.4 0.4) 
	ylabel(-.1(0.1).3, format(`fmt') angle(vertical) grid gmax gmin)
	xlabel(-4(1)5, nogrid)
	xline(-0.5, lc(black) lp(solid) ) yline(0, lc(black) lp(solid) )
	mfc(none none)
	lpattern(solid dash)
	legend(
			ring(1) position(6) cols(2)
			region(lstyle(solid) lwidth(thin) lcolor(black))
		)
	graphregion(color(white)) bgcolor(white);	
	
#delimit cr

gr export $out/FigureA11_A.pdf, replace
}

***************************************
**# Figure A12
* Propensity Score Distribution
***************************************
{
use $data/firm_matched_list, replace

* Define bin width and range
local bw = 0.01

* Create bins
gen bin = floor(pscore_og / `bw')

* Compute bin midpoint
gen bin_mid = (bin * `bw') + (`bw'/2)

* Frequencies for treated and control
collapse (count) freq = pscore_og, by(bin bin_mid treat)

* Reshape to wide format: treated vs control
reshape wide freq, i(bin bin_mid) j(treat)

* Rename for clarity
replace freq0 = 0 if mi(freq0)
replace freq1 = 0 if mi(freq1)

gegen tot_control = sum(freq0)
gegen tot_treated = sum(freq1)

* drop obs counts < 10 for vetting purposes
drop if freq0 < 10 | freq1 < 10

replace freq0 = freq0/tot_control
replace freq1 = freq1/tot_treated

rename freq0 freq_control
rename freq1 freq_treated

order bin bin_mid freq_treated freq_control
sort bin

keep if bin >=0

save $out/firm_pscore_overlap.dta, replace

* Create bar boundaries
gen bin_left  = bin_mid - `bw'/2
gen bin_right = bin_mid + `bw'/2

* Plot manually using twoway bar
# delimit ;
twoway 
    (bar freq_treated bin_mid, barwidth(`bw') color(blue) fcolor(blue%10) 
        legend(label(1 "Treated Units"))) 
    (bar freq_control bin_mid, barwidth(`bw') color(yellow) fcolor(red%10) 
        legend(label(2 "Control Units"))) 
    , 
    title("Propensity Score Overlap Plot") 
    xtitle("Estimated Propensity Score") 
    ytitle("Share") 
    xlabel(0(0.01)0.07) 
    legend(ring(0) pos(2) col(1)) 
	ylabel(, format(%9.2fc) grid gmax gmin)
	graphregion(color(white)) bgcolor(white);
#delimit cr

graph export $out/FigureA12_A.pdf, replace
	
***************************************
* Matching M&A workers with control workers
***************************************
use $data/worker_matched_list, clear

* Define bin width and range
local bw = 0.02

* Create bins
gen bin = floor(pscore_og / `bw')

* Keep only bins 0–9 (corresponding to 0–1)
keep if bin >= 0 & bin <= 100

* Compute bin midpoint
gen bin_mid = (bin * `bw') + (`bw'/2)

* Frequencies for treated and control
collapse (count) freq = pscore_og, by(bin bin_mid treat)

* Reshape to wide format: treated vs control
reshape wide freq, i(bin bin_mid) j(treat)

* Rename for clarity
replace freq0 = 0 if mi(freq0)
replace freq1 = 0 if mi(freq1)

gegen tot_control = sum(freq0)
gegen tot_treated = sum(freq1)

* drop obs counts < 10 for vetting purposes
drop if freq0 < 10 | freq1 < 10

replace freq0 = freq0/tot_control
replace freq1 = freq1/tot_treated

rename freq0 freq_control
rename freq1 freq_treated

order bin bin_mid freq_treated freq_control
sort bin

save $out/worker_pscore_overlap.dta, replace

* Create bar boundaries
gen bin_left  = bin_mid - `bw'/2
gen bin_right = bin_mid + `bw'/2

* Plot manually using twoway bar
#delimit ;
twoway 
    (bar freq_treated bin_mid, barwidth(`bw') color(blue) fcolor(blue%10) 
        legend(label(1 "Treated Units"))) 
    (bar freq_control bin_mid, barwidth(`bw') color(yellow) fcolor(red%10) 
        legend(label(2 "Control Units"))) 
    , 
    title("Propensity Score Overlap Plot") 
    xtitle("Estimated Propensity Score") 
    ytitle("Share") 
    xlabel(0(0.1)1) 
    legend(ring(0) pos(2) col(1))
	ylabel(, format(%9.2fc) grid gmax gmin)
	graphregion(color(white)) bgcolor(white);

#delimit cr

graph export $out/FigureA12_B.pdf, replace
}
